﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Dapper;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.OracleClient;
using _180FrameWork.DataBaseHelper.Entities;

namespace _180FrameWork.DataBaseHelper.OracleExtend
{
    public class OracleJintDB : JintDB, IJintDB
    {
        public OracleJintDB(string DBConnectionString)
        {
            _db = new Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase(DBConnectionString);
            _paramChar = ":";
        }

        #region Dapper

        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public override PageData<dynamic> Page(string sql, IDictionary<string, object> param, System.Data.IDbTransaction tran)
        {
            int page = 1; int rows = 20; string sort = ""; string order = ""; int total = 0; string SUM = "";
            if (param != null)
            {
                foreach (KeyValuePair<string, object> item in param)
                {
                    switch (item.Key)
                    {
                        case "page":
                            page = Convert.ToInt32(item.Value);
                            break;
                        case "rows":
                            rows = Convert.ToInt32(item.Value);
                            break;
                        case "sort":
                            sort = Convert.ToString(item.Value);
                            break;
                        case "order":
                            order = Convert.ToString(item.Value);
                            break;
                        case "total":
                            total = Convert.ToInt32(item.Value);
                            break;
                        case "sum":
                            SUM = Convert.ToString(item.Value);
                            break;
                        //default:
                        //    dp.Add(item.Key, item.Value);
                        //    break;
                    }
                }
            }
            var dp = GetParams(sql, param, false);
            var p = new PageData<dynamic>();
            if (total != 0)
            {
                p.total = total;
            }
            else
            {
                string strTotalSql = "select count(*) from(" + sql + ") t";
                p.total = this.Conn.ExecuteScalar<int>(strTotalSql, dp, tran);
                //if (p.total == 0)
                //    p.total = 1;
            }
            p.totalPages = (p.total / rows) + (p.total % rows > 0 ? 1 : 0);
            //汇总
            if (SUM != "")
            {
                string strSumSql = "select " + SUM + " from(" + sql + ") t";
                p.footer = this.Conn.Query(strSumSql, dp, tran).ToList();
            }
            //排序
            string orderby = "";
            if (sort != "")
            {
                orderby = " order by " + sort;
                if (order != "")
                {
                    orderby += " " + order;
                }
            }
            //获取分页数据
            int beginrow = rows * (page - 1) + 1;
            int endrow = rows * page;
            dp.Add("beginrow", beginrow);
            dp.Add("endrow", endrow);
            string strDataSql = "select c.* from(select t.*,ROWNUM AS RN from(select x.* from(" + sql + ")x " + orderby + " )t where rownum<=:ENDROW)c WHERE C.RN>=:BEGINROW ";
            p.rows = this.Conn.Query(strDataSql, dp, tran).ToList();
            return p;
        }

        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="param"></param>
        public override IDictionary<string, object> Sp(string spName, IDictionary<string, object>[] param)
        {
            IDictionary<string, object> result = new Dictionary<string, object>();
            var p = GetOracleDynamicParameters(param);
            int listResultNumber = 0;
            foreach (var item in p.OracleParameters)
            {
                if (item.OracleType == OracleType.Cursor)
                {
                    listResultNumber++;
                }
            }
            if (listResultNumber == 0)
            {
                this.Conn.Execute(spName, p, null, null, CommandType.StoredProcedure);
                foreach (var item in p.OracleParameters)
                {
                    if (item.Direction == ParameterDirection.Output || item.Direction == ParameterDirection.InputOutput)
                    {
                        result.Add(item.ParameterName, item.Value);
                    }
                }
            }
            else
            {
                var gr = this.Conn.QueryMultiple(spName, p, null, null, CommandType.StoredProcedure);
                foreach (var item in p.OracleParameters)
                {

                    if (item.Direction == ParameterDirection.Output || item.Direction == ParameterDirection.InputOutput)
                    {
                        if (item.OracleType == OracleType.Cursor)
                        {
                            result.Add(item.ParameterName, gr.Read().ToList());
                        }
                        else
                        {
                            result.Add(item.ParameterName, item.Value);
                        }
                    }
                }
            }
            return result;
        }        
        
        private OracleDynamicParameters GetOracleDynamicParameters(IDictionary<string, object>[] param)
        {
            OracleDynamicParameters p = new OracleDynamicParameters();
            if (param != null)
            {
                foreach (var item in param)
                {
                    string pn = item.ContainsKey("pn") ? item["pn"].ToString() : "";
                    if (pn != "")
                    {
                        object pv = item.ContainsKey("pv") ? item["pv"] : null;
                        OracleType pt = item.ContainsKey("pt") ? GetOracleType(item["pt"].ToString()) : OracleType.VarChar;
                        int ps = item.ContainsKey("ps") ? Convert.ToInt32(item["ps"]) : 0;
                        ParameterDirection pd = item.ContainsKey("pd") ? GetParameterDirection(item["pd"].ToString()) : ParameterDirection.Input;
                        p.Add(pn, pv, pt, ps, pd);
                    }
                }
            }
            return p;
        }
        private OracleType GetOracleType(string pt)
        {
            OracleType result = OracleType.VarChar;
            switch (pt.ToUpper())
            {
                case "BFILE": result = OracleType.BFile; break;
                case "BLOB": result = OracleType.Blob; break;
                case "CHAR": result = OracleType.Char; break;
                case "CLOB": result = OracleType.Clob; break;
                case "CURSOR": result = OracleType.Cursor; break;
                case "DATETIME": result = OracleType.DateTime; break;
                case "INTERVALDAYTOSECOND": result = OracleType.IntervalDayToSecond; break;
                case "INTERVALYEARTOMONTH": result = OracleType.IntervalYearToMonth; break;
                case "LONGRAW": result = OracleType.LongRaw; break;
                case "LONGVARCHAR": result = OracleType.LongVarChar; break;
                case "NCHAR": result = OracleType.NChar; break;
                case "NCLOB": result = OracleType.NClob; break;
                case "NUMBER": result = OracleType.Number; break;
                case "NVARCHAR": result = OracleType.NVarChar; break;
                case "RAW": result = OracleType.Raw; break;
                case "ROWID": result = OracleType.RowId; break;
                case "TIMESTAMP": result = OracleType.Timestamp; break;
                case "TIMESTAMPLOCAL": result = OracleType.TimestampLocal; break;
                case "TIMESTAMPWITHTZ": result = OracleType.TimestampWithTZ; break;
                case "VARCHAR": result = OracleType.VarChar; break;
                case "BYTE": result = OracleType.Byte; break;
                case "UINT16": result = OracleType.UInt16; break;
                case "UINT32": result = OracleType.UInt32; break;
                case "SBYTE": result = OracleType.SByte; break;
                case "INT16": result = OracleType.Int16; break;
                case "INT32": result = OracleType.Int32; break;
                case "FLOAT": result = OracleType.Float; break;
                case "DOUBLE": result = OracleType.Double; break;

            }
            return result;
        }
        private ParameterDirection GetParameterDirection(string pd)
        {
            ParameterDirection result = ParameterDirection.Input;
            switch (pd.ToUpper())
            {
                case "IN":
                    result = ParameterDirection.Input;
                    break;
                case "OUT":
                    result = ParameterDirection.Output;
                    break;
                case "INOUT":
                    result = ParameterDirection.InputOutput;
                    break;
                case "RETURN":
                    result = ParameterDirection.ReturnValue;
                    break;
            }
            return result;
        }
        /// <summary>
        /// 通过sql语句获取参数
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        protected override DynamicParameters GetParams(string SQL, IDictionary<string, object> data,bool StrToDateTime)
        {
            var param = new DynamicParameters();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();
                if (data != null)
                {
                    foreach (KeyValuePair<string, object> prop in data)
                    {
                        foreach (var Key in mapList)
                        {
                            if (Key.ToUpper() == this._paramChar + prop.Key.ToUpper())
                            {
                                if (prop.Value != null)
                                {
                                    if (prop.Value.GetType().Name.ToUpper() == "STRING")
                                    {
                                        string strValue = prop.Value.ToString();
                                        int byteCount = System.Text.Encoding.Default.GetByteCount(strValue);
                                        if (byteCount > 4000)
                                        {
                                            byte[] b;
                                            //如果是base64则转换成byte存储
                                            if (IsBase64(prop.Value.ToString(), out b))
                                            {
                                                param.Add(Key, b);
                                            }
                                            else
                                            {
                                                param.Add(Key, new OracleClobParameter(prop.Value));
                                            }
                                        }
                                        else
                                        {
                                            if (StrToDateTime)
                                            {
                                                //如果是日期格式的则进行转换
                                                DateTime dt;
                                                if (IsDateTime(prop.Value.ToString(), out dt))
                                                {
                                                    param.Add(Key, dt);
                                                }
                                                else
                                                {
                                                    param.Add(Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                                                }
                                            }
                                            else
                                            {
                                                param.Add(Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                                            }
                                        }
                                    }
                                    else
                                    {
                                        param.Add(Key, prop.Value);
                                    }
                                }
                                else
                                {
                                    param.Add(Key, null);
                                }
                            }
                        }
                    }
                }                
            }
            return param;
        }
        #region 20220218之前的获取参数操作
        /*
        protected override DynamicParameters GetParams(string SQL, IDictionary<string, object> data)
        {
            var param = new DynamicParameters();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (mapList.Contains(this._paramChar + prop.Key))
                    {
                        if (prop.Value != null)
                        {
                            if (prop.Value.GetType().Name.ToUpper() == "STRING")
                            {
                                string strValue = prop.Value.ToString();
                                int byteCount = System.Text.Encoding.Default.GetByteCount(strValue);
                                if (byteCount > 4000)
                                {
                                    byte[] b;
                                    if (IsBase64(prop.Value.ToString(), out b))
                                    {
                                        param.Add(prop.Key, b);
                                    }
                                    else
                                    {
                                        param.Add(prop.Key, new OracleClobParameter(prop.Value));
                                    }
                                }
                                else
                                {
                                    param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                                }
                            }
                            else
                            {
                                param.Add(prop.Key, prop.Value);
                            }
                        }
                        else
                        {
                            param.Add(prop.Key, null);
                        }
                    }
                }
            }
            else
            {

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (prop.Value != null)
                    {
                        if (prop.Value.GetType().Name.ToUpper() == "STRING")
                        {
                            param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                        }
                        else
                        {
                            param.Add(prop.Key, prop.Value.ToString());
                        }
                    }
                    else
                    {
                        param.Add(prop.Key, null);
                    }
                }
            }
            return param;
        }
       */
        #endregion
        

        protected override DataBaseTableSchedule GetTableSchedule(string tableName, IDbTransaction tran)
        {
           
            //是否需要拆分
            if (tableName.Contains("."))
            {
                var list = tableName.Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
                string owner = list[0].ToUpper();
                string realTableName = list[1].ToUpper();
                var colList = this.Conn.Query<DataBaseColumnSchedule>(@"select 
cols.column_name as ColumnName,case when pkcols.column_name is null then 0 else 1 end as IsPrimaryKey
from all_tab_columns cols 
left join (select conscols.column_name from all_cons_columns conscols 
inner join all_constraints cons on conscols.owner=cons.owner and conscols.constraint_name=cons.constraint_name and cons.constraint_type='P'
where conscols.table_name=:tableName and conscols.owner=:owner) pkcols on cols.column_name=pkcols.column_name
where  cols.table_name=:tableName and cols.owner=:owner", new { tableName = realTableName.ToUpper(), owner = owner.ToUpper() }, tran).ToList();
                return new DataBaseTableSchedule(colList);
            }
            else
            {
                var colList = this.Conn.Query<DataBaseColumnSchedule>(@"select 
cols.column_name as ColumnName,case when pkcols.column_name is null then 0 else 1 end as IsPrimaryKey
from all_tab_columns cols 
left join (select conscols.column_name from all_cons_columns conscols 
inner join all_constraints cons on conscols.owner=cons.owner and conscols.constraint_name=cons.constraint_name and cons.constraint_type='P'
where conscols.table_name=:tableName) pkcols on cols.column_name=pkcols.column_name
where  cols.table_name=:tableName", new { tableName = tableName.ToUpper() }, tran).ToList();
                return new DataBaseTableSchedule(colList);
            }
        }

//        protected override IList<string> GetPKList(string tableName, IDbTransaction tran)
//        {
//            //是否需要拆分
//            if (tableName.Contains("."))
//            {
//                var list = tableName.Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
//                string owner = list[0].ToUpper();
//                string realTableName = list[1].ToUpper();
//                return this.Conn.Query<string>(@"select  
//cols.column_name
//from all_constraints cons inner join all_cons_columns cols on cons.constraint_name=cols.constraint_name and cons.owner=cols.owner
//where cons.constraint_type='P' and cons.table_name=:tableName and cons.owner=:owner", new
//                                                                                    {
//                                                                                        tableName = realTableName,
//                                                                                        owner = owner
//                                                                                    }, tran).ToList();

//            }
//            else
//            {
//                return this.Conn.Query<string>(@"select  
//cols.column_name
//from all_constraints cons inner join all_cons_columns cols on cons.constraint_name=cols.constraint_name and cons.owner=cols.owner
//where cons.constraint_type='P' and cons.table_name=:tableName", new { tableName = tableName.ToUpper() }, tran).ToList();

//            }
//        }
        #endregion
    }
}
